﻿Imports System.Data.SqlClient

Public Class frmRenewalsAssign

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Sub FillRenewal1()
        Try
            Me.Cursor = Cursors.WaitCursor

            'Get renewals list
            Dim cmdGetRenewalList As New SqlCommand("Insert Into Renewal (OrginalSNo,ClientID,ClientName,InsuranceCompany,PolicyNo," & _
                                                    "ClassofBusiness,IssueDate,ExpiryDate) Select SNo,ClientNo,ClientName,InsurComp,PolicyNo," & _
                                                    "ClassName,PeriodFrom,PeriodTo From Policies Where DateDiff(d,GetDate(),PeriodTo)<=75", cnn)

            cnn.Open()
            cmdGetRenewalList.ExecuteNonQuery()
            cnn.Close()
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillRenewal2()
        Try
            Me.Cursor = Cursors.WaitCursor

            'Check if Assigned/Unassigned
            Dim Status As String = ""
            If Me.RActive.Checked = True Then
                Status = "Active"

            ElseIf Me.RNotActive.Checked = True Then
                Status = "Not Active"
            End If

            'Fill renewals
            Dim cmd As New SqlCommand("Select SNo,Status,ClientID,ClientName,InsuranceCompany,PolicyNo,ClassofBusiness,IssueDate,ExpiryDate" & _
                                      ",IsNull(AssignedTo,N'') AssignedTo,IsNull(AssignedBy,N'') AssignedBy," & _
                                      "AssignedDate From Renewal Where Status=N'" & Status & "' Order By ExpiryDate", cnn)
            Dim Reader As SqlDataReader
            Dim AssignDate As String

            Me.GridRenewals.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                If Reader.Item("AssignedDate").ToString = "1/1/1900 12:00:00 AM" Then
                    AssignDate = ""
                ElseIf Reader.Item("AssignedDate").ToString = "" Then
                    AssignDate = ""
                Else
                    AssignDate = CDate(Reader.Item("AssignedDate")).ToString("yyyy/MM/dd")
                End If

                Me.GridRenewals.Rows.Add(New String() {Reader.Item("SNo"), "N", Reader.Item("Status"), Reader.Item("ClientID"), Reader.Item("ClientName"), _
                                                       Reader.Item("InsuranceCompany"), Reader.Item("PolicyNo"), _
                                                       Reader.Item("ClassofBusiness"), CDate(Reader.Item("IssueDate")).ToString("yyyy/MM/dd"), _
                                                       CDate(Reader.Item("ExpiryDate")).ToString("yyyy/MM/dd"), _
                                                       Reader.Item("AssignedTo"), Reader.Item("AssignedBy"), AssignDate})
            End While
            cnn.Close()

            'Check Colors
            For Each row As DataGridViewRow In Me.GridRenewals.Rows
                If row.Cells(2).Value = "Active" Then
                    row.Cells(2).Style.BackColor = Color.Yellow

                ElseIf row.Cells(2).Value = "Not Active" Then
                    row.Cells(2).Style.BackColor = Color.Magenta
                End If
            Next

            'Check Expiry Date
            For Each Row As DataGridViewRow In Me.GridRenewals.Rows
                If Now.Date >= CType(Row.Cells(9).Value, Date).Date Then
                    Row.Cells(9).Style.BackColor = Color.Red
                Else
                    Row.Cells(9).Style.BackColor = Color.White
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillUsersList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select FullName,Email From Users Order By FullName", cnn)
            Dim Reader As SqlDataReader

            Me.ListSalesAgents.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("FullName")
                Item.SubItems.Add(Reader.Item("Email"))

                Me.ListSalesAgents.Items.Add(Item)
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmRenewalsAssign_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        FillUsersList()
        FillRenewal1()
        FillRenewal2()
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If Me.ListSalesAgents.SelectedItems.Count = 0 Then
            MsgBox("Please select a sales agent from the list")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction
                Dim X As Integer = 0

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                For Each row As DataGridViewRow In Me.GridRenewals.Rows
                    If row.Cells(1).Value = "Y" Then
                        cmd.CommandText = "Update Renewal Set Status=@Status,AssignedTo=@AssignedTo,AssignedBy=@AssignedBy," & _
                      "AssignedDate=GetDate() Where SNo=" & CInt(row.Cells(0).Value)

                        'Add Values
                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("@Status", "Active")
                        cmd.Parameters.AddWithValue("@AssignedTo", Me.ListSalesAgents.SelectedItems(0).Text)
                        cmd.Parameters.AddWithValue("@AssignedBy", CurrentUser)
                        cmd.ExecuteNonQuery()
                        X += 1
                    End If
                Next

                Trans.Commit()
                cnn.Close()

                'Sent notification if prospects have been assigned
                If X > 0 Then
                    Dim Subject, Body As String
                    Dim Receivers As New ArrayList

                    Subject = "Oasis - IBS Notification - Sales Management System"
                    Body = "Your received a notification from: <B>" & CurrentUser & "</B>. " & _
                           "<B>New  renewals have been assigned to you.</B><br/><br/>" & _
                           "Please don't reply to this email.<br/>" & _
                           "Sent from Oasis - Insurance Broker System"
                    Receivers.Add(Me.ListSalesAgents.SelectedItems(0).SubItems(1).Text)

                    SendEmail(Subject, Body, Receivers)

                    MsgBox("Notification has been sent successfully")
                End If

                FillRenewal2()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub RNotActive_CheckedChanged(sender As System.Object, e As System.EventArgs)
        FillRenewal2()
    End Sub

    Private Sub RActive_CheckedChanged(sender As System.Object, e As System.EventArgs)
        FillRenewal2()
    End Sub
End Class